package com.example.homework.db

import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import com.example.homework.model.User

object DBConst {
    const val DB_NAME        = "snake_db"
    const val DB_VERSION     = 2
    const val TABLE_USER     = "user"
    const val COL_ACCOUNT    = "account"
    const val COL_USERNAME   = "username"
    const val COL_AVATAR     = "avatar"
    const val COL_HIGH_SCORE = "high_score"
}

class UserDBHelper(ctx: Context) : SQLiteOpenHelper(ctx, DBConst.DB_NAME, null, DBConst.DB_VERSION) {

    inline fun <R> UserDBHelper.use(block: (UserDBHelper) -> R): R {
        return try { block(this) } finally { close() }
    }

    override fun onCreate(db: SQLiteDatabase) {
        val sql = """
            CREATE TABLE ${DBConst.TABLE_USER}(
                ${DBConst.COL_ACCOUNT}  TEXT PRIMARY KEY,
                ${DBConst.COL_USERNAME} TEXT,
                ${DBConst.COL_AVATAR}   TEXT,
                ${DBConst.COL_HIGH_SCORE} INTEGER DEFAULT 0
            )
        """.trimIndent()
        db.execSQL(sql)
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        // 简单粗暴：老表丢弃重建（生产环境请做迁移）
        db.execSQL("DROP TABLE IF EXISTS ${DBConst.TABLE_USER}")
        onCreate(db)
    }

    /* 注册 or 登录：根据 account 插入/查询 */
    fun loginOrRegister(account: String, username: String, avatar: String): User {
        val db = readableDatabase
        val cursor = db.query(
            DBConst.TABLE_USER,
            null,
            "${DBConst.COL_ACCOUNT}=?",
            arrayOf(account),
            null, null, null
        )

        return if (cursor.moveToFirst()) {
            // 已存在 -> 直接返回
            val name  = cursor.getString(cursor.getColumnIndexOrThrow(DBConst.COL_USERNAME))
            val ava   = cursor.getString(cursor.getColumnIndexOrThrow(DBConst.COL_AVATAR))
            val score = cursor.getInt(cursor.getColumnIndexOrThrow(DBConst.COL_HIGH_SCORE))
            cursor.close()
            User(account, name, ava, score)
        } else {
            cursor.close()
            // 新账号 -> 插入
            val values = ContentValues().apply {
                put(DBConst.COL_ACCOUNT,  account)
                put(DBConst.COL_USERNAME, username)
                put(DBConst.COL_AVATAR,   avatar)
                put(DBConst.COL_HIGH_SCORE, 0)
            }
            db.insert(DBConst.TABLE_USER, null, values)
            User(account, username, avatar, 0)
        }
    }

    /* 更新最高分 */
    fun updateHighScore(account: String, newScore: Int) {
        val db = writableDatabase
        val cv = ContentValues().apply { put(DBConst.COL_HIGH_SCORE, newScore) }
        db.update(DBConst.TABLE_USER, cv, "${DBConst.COL_ACCOUNT}=?", arrayOf(account))
    }

    /* 查询最后一次登录的账号 */
    fun getUserByAccount(account: String): User? {
        val db = readableDatabase
        val c = db.query(
            DBConst.TABLE_USER,
            null,
            "${DBConst.COL_ACCOUNT}=?",
            arrayOf(account),          // ← 正确
            null, null, null
        )
        return c.use {                // 自动 close
            if (it.moveToFirst()) {
                User(
                    it.getString(it.getColumnIndexOrThrow(DBConst.COL_ACCOUNT)),
                    it.getString(it.getColumnIndexOrThrow(DBConst.COL_USERNAME)),
                    it.getString(it.getColumnIndexOrThrow(DBConst.COL_AVATAR)),
                    it.getInt   (it.getColumnIndexOrThrow(DBConst.COL_HIGH_SCORE))
                )
            } else null
        }
    }

    /* 更新昵称 */
    fun updateUsername(account: String, newName: String) {
        val db = writableDatabase
        val cv = ContentValues().apply { put(DBConst.COL_USERNAME, newName) }
        db.update(DBConst.TABLE_USER, cv, "${DBConst.COL_ACCOUNT}=?", arrayOf(account))
    }

    /* 更新头像 */
    fun updateAvatar(account: String, newAvatar: String) {
        val db = writableDatabase
        val cv = ContentValues().apply { put(DBConst.COL_AVATAR, newAvatar) }
        db.update(DBConst.TABLE_USER, cv, "${DBConst.COL_ACCOUNT}=?", arrayOf(account))
    }

}